package com.hsnn.medstgmini.subscribe.dao.impl;

import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Query;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.hsnn.medstgmini.base.dict.dao.impl.BaseHibernateDAO;
import com.hsnn.medstgmini.subscribe.dao.BidSubscribeDetailDao;
import com.hsnn.medstgmini.subscribe.dao.BidSubscribeRecordDao;
import com.hsnn.medstgmini.subscribe.model.BidSubscribeDetail;
import com.hsnn.medstgmini.subscribe.model.BidSubscribeRecord;
import com.hsnn.medstgmini.util.Pagination;

/**
 * @category 预约记录接口DAO 实现类
 * @author 韩守松
 * @date 2015年8月31日
 */
@Repository
public class BidSubscribeRecordDaoImpl extends BaseHibernateDAO implements BidSubscribeRecordDao{

	@Autowired
	private BidSubscribeDetailDao bidSubscribeDetailDao;
	/**
	 * @category 保存预约企业信息
	 * @author 韩守松
	 * @date   2015年9月1日
	 * @param  @param bidSubscribeRecord
	 */
	public void save(BidSubscribeRecord bidSubscribeRecord) {
		getSession().saveOrUpdate(bidSubscribeRecord);
		getSession().flush();
		
		if(StringUtils.isNotBlank(bidSubscribeRecord.getRecordId())){
			StringBuffer hqlDetail = new StringBuffer();
			hqlDetail.append(" update bid_subscribe_detail set sub_count = (sub_count-1),upd_datetime = :updDatetime , upd_user = :updUser where bid_subscribe_detail_id = :bidSubscribeDetailId ");
			Query queryDetail = getSession().createSQLQuery(hqlDetail.toString());
			queryDetail.setTimestamp("updDatetime", bidSubscribeRecord.getUpdDatetime());
			queryDetail.setString("updUser", bidSubscribeRecord.getUpdUser());
			queryDetail.setString("bidSubscribeDetailId", bidSubscribeRecord.getDetailId());
			queryDetail.executeUpdate();
			getSession().flush();
			
			getSession().clear();
			StringBuffer hqlDetailLog = new StringBuffer();
			hqlDetailLog.append("insert into bid_subscribe_detail_log  ")
			   .append(" select UUID(),bsd.* from bid_subscribe_detail bsd where bsd.bid_subscribe_detail_id= :bidSubscribeDetailId ");
			Query queryDetailLog = getSession().createSQLQuery(hqlDetailLog.toString());
			queryDetailLog.setString("bidSubscribeDetailId", bidSubscribeRecord.getDetailId());
			queryDetailLog.executeUpdate();
			getSession().flush();
		}
		
		getSession().clear();
		StringBuffer hqlLog = new StringBuffer();
		hqlLog.append("insert into bid_subscribe_record_log  ")
		   .append(" select UUID(),bsr.* from bid_subscribe_record bsr where bsr.bid_subscribe_record_id= :bidSubscribeRecordId ");
		Query queryLog = getSession().createSQLQuery(hqlLog.toString());
		queryLog.setString("bidSubscribeRecordId", bidSubscribeRecord.getBidSubscribeRecordId());
		queryLog.executeUpdate();
		getSession().flush();
	}
	
	/**
	 * @category 获取生产企业预约记录信息
	 * @author 韩守松
	 * @date   2015年9月1日
	 * @param  @param page
	 * @param  @param bidSubscribeDetail
	 * @param  @param bidSubscribeRecord
	 * @param  @return
	 */
	public Pagination getSubscribeRecordList(Pagination page,BidSubscribeDetail bidSubscribeDetail,BidSubscribeRecord bidSubscribeRecord) {
		StringBuffer hql = new StringBuffer();
		StringBuffer hqlCount = new StringBuffer();
		hql.append(" select ");
		hql.append(" bsr.bid_subscribe_record_id bidSubscribeRecordId,");
		hql.append(" bsr.bid_subscribe_detail_id bidSubscribeDetailId,bsd.bid_subscribe_id bidSubscribeId, ");
		hql.append(" bs.proj_id projId,bp.name as projName,DATE_FORMAT(bsd.subscribe_date,'%Y-%m-%d') subscribeDateStr, ");
		hql.append(" DATE_FORMAT(bsd.subscribe_starttime,'%H:%i:%S') subscribeStarttimeStr,DATE_FORMAT(bsd.subscribe_endtime,'%H:%i:%S') subscribeEndtimeStr ");
		hql.append(" from  bid_subscribe_record bsr ");
		hql.append(" left join bid_subscribe_detail bsd on bsr.bid_subscribe_detail_id = bsd.bid_subscribe_detail_id ");
		hql.append(" left join bid_subscribe bs on bsd.bid_subscribe_id = bs.bid_subscribe_id ");
		hql.append(" left join bid_project bp on bs.proj_id = bp.proj_id ");
		hql.append(" where bsr.comp_id = :compId ");
		hql.append(" and bs.status = 1 ");
		hqlCount.append(" select count(*) ");
		hqlCount.append(" from  bid_subscribe_record bsr ");
		hqlCount.append(" left join bid_subscribe_detail bsd on bsr.bid_subscribe_detail_id = bsd.bid_subscribe_detail_id ");
		hqlCount.append(" left join bid_subscribe bs on bsd.bid_subscribe_id = bs.bid_subscribe_id ");
		hqlCount.append(" left join bid_project bp on bs.proj_id = bp.proj_id ");
		hqlCount.append(" where bsr.comp_id = :compId ");
		hqlCount.append(" and bs.status = 1 ");
		if(StringUtils.isNotBlank(bidSubscribeRecord.getProjId())){
			hql.append(" and bs.proj_id = :projId ");
			hqlCount.append(" and bs.proj_id = :projId ");
		}
		if(null != bidSubscribeDetail.getSubscribeDate()){
			hql.append(" and bsd.subscribe_date = :subscribeDate ");
			hqlCount.append(" and bsd.subscribe_date = :subscribeDate ");
		}
		
		hql.append(" order by bsr.upd_datetime desc ");
		Query countQuery = getSession().createSQLQuery(hqlCount.toString());
		countQuery.setString("compId", bidSubscribeRecord.getCompId());
		if(StringUtils.isNotBlank(bidSubscribeRecord.getProjId())){
			countQuery.setString("projId",bidSubscribeRecord.getProjId());
		}
		if(null != bidSubscribeDetail.getSubscribeDate()){
			countQuery.setDate("subscribeDate",bidSubscribeDetail.getSubscribeDate());
		}
		page.setRecords(Long.valueOf(countQuery.uniqueResult().toString()));
		Query query = getSession().createSQLQuery(hql.toString());
		query.setString("compId", bidSubscribeRecord.getCompId());
		if(StringUtils.isNotBlank(bidSubscribeRecord.getProjId())){
			query.setString("projId",bidSubscribeRecord.getProjId());
		}
		if(null != bidSubscribeDetail.getSubscribeDate()){
			query.setDate("subscribeDate",bidSubscribeDetail.getSubscribeDate());
		}
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		query.setFirstResult((page.getPage()-1)*page.getMaxResults());
		query.setMaxResults(page.getMaxResults());
		page.setRows(query.list());
		return page;
	}
	
	
	/**
	 * @category 更新企业记录信息
	 * @author 韩守松
	 * @date   2015年9月2日
	 * @param  @param bidSubscribeRecord
	 * @param  @param bidSubscribeDetail
	 * @param  @return
	 */
	public void update(BidSubscribeRecord bidSubscribeRecord) {
		StringBuffer hql = new StringBuffer();
		hql.append(" update bid_subscribe_record set process_status = 1,process_datetime = :processDatetime ,upd_datetime = :updDatetime,upd_user = :updUser  where bid_subscribe_record_id = :bidSubscribeRecordId ");
		Query query = getSession().createSQLQuery(hql.toString());
		query.setTimestamp("processDatetime",bidSubscribeRecord.getProcessDatetime());
		query.setTimestamp("updDatetime", bidSubscribeRecord.getUpdDatetime());
		query.setString("updUser", bidSubscribeRecord.getUpdUser());
		query.setString("bidSubscribeRecordId", bidSubscribeRecord.getBidSubscribeRecordId());
		query.executeUpdate();
		getSession().flush();
		
		
		getSession().clear();
		StringBuffer hqlLog = new StringBuffer();
		hqlLog.append("insert into bid_subscribe_record_log  ")
		   .append(" select UUID(),bsr.* from bid_subscribe_record bsr where bsr.bid_subscribe_record_id= :bidSubscribeRecordId ");
		Query queryLog = getSession().createSQLQuery(hqlLog.toString());
		queryLog.setString("bidSubscribeRecordId", bidSubscribeRecord.getBidSubscribeRecordId());
		queryLog.executeUpdate();
		getSession().flush();
		
	}
	
	
	/**
	 * @category 获取指定详情id 企业数量
	 * @author 韩守松
	 * @date   2015年9月7日
	 * @param  @param bidSubscribeDetailId
	 * @param  @return
	 */
	public Integer getCompIdNumber(String bidSubscribeDetailId) {
		StringBuffer hql = new StringBuffer();
		hql.append(" select count(*) from bid_subscribe_record  where bid_subscribe_detail_id = :bidSubscribeDetailId ");
		Query query = getSession().createSQLQuery(hql.toString());
		query.setString("bidSubscribeDetailId", bidSubscribeDetailId);
		Integer number = Integer.valueOf(query.list().get(0).toString());
		return number;
	}
	
	/**
	 * @category 获取已预约过的企业数
	 * @author 韩守松
	 * @date   2015年9月17日
	 * @param  @param compId
	 * @param  @param bidSubscribeId
	 * @param  @return
	 */
	public String getCompNum(String compId,String bidSubscribeId,String bidSubscribeDetailId) {
		StringBuffer hql = new StringBuffer();
		hql.append(" select ");
		hql.append(" bsr.bid_subscribe_record_id ");
		hql.append(" from bid_subscribe_record bsr ");
		hql.append(" left join bid_subscribe_detail bsd on bsr.bid_subscribe_detail_id = bsd.bid_subscribe_detail_id ");
		hql.append(" where  bsr.comp_id = :compId ");
		hql.append(" and  bsd.bid_subscribe_id = :bidSubscribeId ");
		hql.append(" and bsr.bid_subscribe_detail_id = :bidSubscribeDetailId ");
		 
		Query query = getSession().createSQLQuery(hql.toString());
		
		query.setString("compId", compId);
		query.setString("bidSubscribeId", bidSubscribeId);
		query.setString("bidSubscribeDetailId", bidSubscribeDetailId);
		String  result = (String) query.uniqueResult();
		if(StringUtils.isNotBlank(result)){
			return result;
		}else{
			return "";
		}
	}
	
	/**
	 * @category 获取成功预约后地址信息
	 * @author 韩守松
	 * @date   2015年9月17日
	 * @param  @param compId
	 * @param  @param bidSubscribeId
	 * @param  @return
	 */
	@SuppressWarnings("rawtypes")
	public List getSuccessData(String compId,String bidSubscribeId){
		StringBuffer hql = new StringBuffer();
		hql.append(" select ");
		hql.append(" bs.center_address centerAddress, ");
		hql.append(" bp.name projName, ");
		hql.append(" DATE_FORMAT(bsd.subscribe_date,'%Y-%m-%d') subscribeDate, ");
		hql.append(" DATE_FORMAT(bsd.subscribe_starttime,'%H:%i:%S') subscribeStarttime, ");
		hql.append(" DATE_FORMAT(bsd.subscribe_endtime,'%H:%i:%S') subscribeEndtime ");
		hql.append(" from bid_subscribe_record bsr ");
		hql.append(" left join bid_subscribe_detail bsd on bsr.bid_subscribe_detail_id = bsd.bid_subscribe_detail_id ");
		hql.append(" left join bid_subscribe bs on bs.bid_subscribe_id = bsd.bid_subscribe_id ");
		hql.append(" left join bid_project bp on bp.proj_id = bs.proj_id ");
		hql.append(" where  bsr.comp_id = :compId ");
		hql.append(" and  bsd.bid_subscribe_id = :bidSubscribeId ");
		 
		Query query = getSession().createSQLQuery(hql.toString());
		query.setString("compId", compId);
		query.setString("bidSubscribeId", bidSubscribeId);
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		return query.list();
	}
	
	/**
	 * @category 根据预约规则Id 企业Id,及已预约数量不等于0 确定 预约记录中预约详情id
	 * @author 韩守松
	 * @date   2015年9月25日
	 * @param  @param bidSubscribeDetail
	 * @param  @param compId
	 * @param  @return
	 */
	public BidSubscribeRecord getHaveRecordId(BidSubscribeDetail bidSubscribeDetail,String compId) {
		StringBuffer hql = new StringBuffer();
		hql.append(" select ");
		hql.append(" bsr.bid_subscribe_record_id,bsr.bid_subscribe_detail_id,bsr.process_status ");
		hql.append(" from bid_subscribe_record bsr ");
		hql.append(" left join bid_subscribe_detail bsd on bsr.bid_subscribe_detail_id = bsd.bid_subscribe_detail_id ");
		hql.append(" where  bsr.comp_id = :compId ");
		hql.append(" and  bsd.bid_subscribe_id = :bidSubscribeId ");
		hql.append(" and bsd.sub_count != 0 ");
		Query query = getSession().createSQLQuery(hql.toString());
		
		query.setString("compId", compId);
		query.setString("bidSubscribeId", bidSubscribeDetail.getBidSubscribeId());
		Object[] obj=(Object[]) query.uniqueResult();
		
		BidSubscribeRecord bidSubscribeRecord = new BidSubscribeRecord();
		if(null != obj){
		 if(obj.length > 0){
			bidSubscribeRecord.setRecordId(obj[0]==null?"":obj[0].toString());
			bidSubscribeRecord.setDetailId(obj[1]==null?"":obj[1].toString());
			bidSubscribeRecord.setProcessStatus(obj[2]==null?0:Integer.valueOf(obj[2].toString()));
		 }
		}
		return bidSubscribeRecord;
	}
}
